﻿/*-----------------------------------------------------------------------
 * <copyright file="DataProvider.cs" company="O-RID">
 *     Copyright (c)2010 O-RID Co.,Ltd.
 * </copyright>
 * <author>Zhang Jun</author>
 * <summary>
 *          所有与 全员 操作相关的所有服务
 *          WCFStaffCorrelation.svc服务实现的具体方法
 *          Get（Set）+服务方法名
 *
 *           作成日：07/12/2010
 *                   07/13/2010  增加 全员别 日别/小时别 稼动率 
 *                   06/20/2011  增加 案件名，线路 条件
 *                                    全员别日别生产性查询					
							          全员别期间别生产性查询					

 * </summary>
 *-----------------------------------------------------------------------
 */
namespace Orid.Management.Statistics.Service.OPUnitService.StaffCorrelation
{
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using Orid.Management.Statistics.Service.OPUnitService.StaffCorrelation.ServiceModel;

    /// <summary>
    /// DataProvider
    /// 具体方法
    /// </summary>
    public class DataProvider
    {
        #region 全员别日别生产性
        /// <summary>
        /// 全员别日别生产性
        /// </summary>
        /// <param name="whereParametersList">The where parameters list.</param>
        /// <returns>
        /// 06/20/2011 增加 案件名，线路 条件
        /// 07/06/2011 增加 ImageType名片属性
        /// </returns>
        public static List<StaffEntrySpeedQueryInforData> GetSelectStaffEntrySpeedQueryInfor(List<WhereParameters> whereParametersList) 
        {
            List<StaffEntrySpeedQueryInforData> staffEntryInforList = new List<StaffEntrySpeedQueryInforData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand("EntrySpeedBaseQueryStaffDays", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starDate", whereParametersList[0].WhereentryDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@FinishDate", whereParametersList[0].WhereentryDateTo));
                    cmd.Parameters.Add(new SqlParameter("@ScriptType", SqlDbType.NVarChar));
                    cmd.Parameters["@ScriptType"].Value = whereParametersList[0].WhereScriptType;
                    cmd.Parameters.Add(new SqlParameter("@WordType", SqlDbType.NVarChar));
                    cmd.Parameters["@WordType"].Value = whereParametersList[0].WhereWordType;
                    cmd.Parameters.Add(new SqlParameter("@JobClass", SqlDbType.NVarChar));
                    cmd.Parameters["@JobClass"].Value = whereParametersList[0].WhereJobClass;

                    // 06/20/2011 增加 案件名，线路 条件
                    cmd.Parameters.Add(new SqlParameter("@MatterID", SqlDbType.NVarChar));
                    cmd.Parameters["@MatterID"].Value = whereParametersList[0].WhereMatterName;
                    cmd.Parameters.Add(new SqlParameter("@Lines", SqlDbType.NVarChar));
                    cmd.Parameters["@Lines"].Value = whereParametersList[0].WhereServer;

                    cmd.CommandTimeout = 180;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        staffEntryInforList.Add(new StaffEntrySpeedQueryInforData()
                        {
                            EntryDate = Common.MyToFormat(reaccount, "EntryDate", Common.ClassType.DATETIME, "yyyy-MM-dd"),
                            MatterID = Common.MyIsDBNull(reaccount, "MatterID"),
                            MatterName = Common.MyIsDBNull(reaccount, "MatterName"),
                            ScriptType = Common.MyIsDBNull(reaccount, "ScriptType"),
                            WordType = Common.MyIsDBNull(reaccount, "WordType"),
                            JobClass = Common.MyIsDBNull(reaccount, "JobClass"),
                            Maserver = Common.MyIsDBNull(reaccount, "Maserver"),
                            ChildDataCount = Common.MyToFormat(reaccount, "ChildDataCount", Common.ClassType.DOUBLE, "#,##0"),
                            WordCount = Common.MyToFormat(reaccount, "WordCount", Common.ClassType.DOUBLE , "#,##0"),
                            Duration = Common.MyToFormat(reaccount, "duration", Common.ClassType.DOUBLE , "#,##0.00"),
                            WordEntrySpeed = Common.MyIsDBNull(reaccount, "WordEntrySpeed"),
                            ChildDataEntrySpeed = Common.MyIsDBNull(reaccount, "ChildDataEntrySpeed"),

                            ChildDataEntrySpeedAvg = Common.MyIsDBNull(reaccount, "ChildDataEntrySpeedAvg"),
                            WordEntrySpeedAvg = Common.MyIsDBNull(reaccount, "WordEntrySpeedAvg"),

                            Accuracy = Common.MyToFormat(reaccount, "Accuracy", Common.ClassType.DOUBLE , "%"),
                            OKChildDataCount = Common.MyToFormat(reaccount, "OKChildDataCount", Common.ClassType.DOUBLE , "#,##0"),
                            // 07/06/2011 增加 ImageType名片属性
                            ImageType = Common.MyIsDBNull(reaccount, "ImageType")
                            
                        });
                    }
                    reaccount.Close();
                    conn.Close();
                }
                return staffEntryInforList;

                #endregion SqlCommand
            }
        }

        #endregion 全员别日别生产性

        #region 全员别期间别生产性
        /// <summary>
        /// 全员别 期间别 生产性
        /// </summary>
        /// <param name="whereParametersList">The where parameters list.</param>
        /// <returns>
        /// 06/20/2011 增加 案件名，线路 条件
        /// 07/06/2011 增加 ImageType名片属性
        /// </returns>
        public static List<StaffDurationEntrySpeedQueryInforData> GetSelectStaffDurationEntrySpeedQueryInfor(List<WhereParameters> whereParametersList)
        {
            List<StaffDurationEntrySpeedQueryInforData> staffEntryInforList = new List<StaffDurationEntrySpeedQueryInforData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand("EntrySpeedBaseQueryStaff", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starDate", whereParametersList[0].WhereentryDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@FinishDate", whereParametersList[0].WhereentryDateTo));
                    cmd.Parameters.Add(new SqlParameter("@ScriptType", SqlDbType.NVarChar));
                    cmd.Parameters["@ScriptType"].Value = whereParametersList[0].WhereScriptType;
                    cmd.Parameters.Add(new SqlParameter("@WordType", SqlDbType.NVarChar));
                    cmd.Parameters["@WordType"].Value = whereParametersList[0].WhereWordType;
                    cmd.Parameters.Add(new SqlParameter("@JobClass", SqlDbType.NVarChar));
                    cmd.Parameters["@JobClass"].Value = whereParametersList[0].WhereJobClass;

                    // 06/20/2011 增加 案件名，线路 条件
                    cmd.Parameters.Add(new SqlParameter("@MatterID", SqlDbType.NVarChar));
                    cmd.Parameters["@MatterID"].Value = whereParametersList[0].WhereMatterName;
                    cmd.Parameters.Add(new SqlParameter("@Lines", SqlDbType.NVarChar));
                    cmd.Parameters["@Lines"].Value = whereParametersList[0].WhereServer;

                    cmd.CommandTimeout = 180;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        staffEntryInforList.Add(new StaffDurationEntrySpeedQueryInforData()
                        {
                            MatterID = Common.MyIsDBNull(reaccount, "MatterID"),
                            MatterName = Common.MyIsDBNull(reaccount, "MatterName"),
                            ScriptType = Common.MyIsDBNull(reaccount, "ScriptType"),
                            WordType = Common.MyIsDBNull(reaccount, "WordType"),
                            JobClass = Common.MyIsDBNull(reaccount, "jobClass"),
                            Maserver = Common.MyIsDBNull(reaccount, "Maserver"),
                            ChildDataCount = Common.MyToFormat(reaccount, "ChildDataCount", Common.ClassType.DOUBLE , "#,##0"),
                            WordCount = Common.MyToFormat(reaccount, "WordCount", Common.ClassType.DOUBLE , "#,##0"),
                            Duration = Common.MyToFormat(reaccount, "duration", Common.ClassType.DOUBLE , "#,##0.00"),
                            WordEntrySpeed = Common.MyIsDBNull(reaccount, "WordEntrySpeed"),
                            ChildDataEntrySpeed = Common.MyIsDBNull(reaccount, "ChildDataEntrySpeed"),

                            ChildDataEntrySpeedAvg = Common.MyIsDBNull(reaccount, "ChildDataEntrySpeedAvg"),
                            WordEntrySpeedAvg = Common.MyIsDBNull(reaccount, "WordEntrySpeedAvg"),

                            Accuracy = Common.MyToFormat(reaccount, "Accuracy", Common.ClassType.DOUBLE , "%"),
                            OKChildDataCount = Common.MyToFormat(reaccount, "OKChildDataCount", Common.ClassType.DOUBLE , "#,##0"),
                            // 07/06/2011 增加 ImageType名片属性
                            ImageType = Common.MyIsDBNull(reaccount, "ImageType")

                        });
                    }
                    reaccount.Close();
                    conn.Close();
                }
                return staffEntryInforList;

                #endregion SqlCommand
            }
        }
        #endregion 全员别期间别生产性

        #region 全员别 稼动率
        /// <summary>
        /// 全员别 稼动率查询 _Day
        /// </summary>
        /// <param name="whereParametersList">The where parameters list.</param>
        /// <returns></returns>
        public static List<StaffWorkEfficQueryData> GetSelectStaffWorkEfficiencyDay(List<WhereParameters> whereParametersList)
        {
            List<StaffWorkEfficQueryData> staffWorkEfficiencyList = new List<StaffWorkEfficQueryData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand" 执行存储过程  WorkEfficiencyQueryStaffDays
                using (SqlCommand cmd = new SqlCommand("WorkEfficiencyQueryStaffDays", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starDate", SqlDbType.DateTime));
                    cmd.Parameters["@starDate"].Value = whereParametersList[0].WhereentryDateFrom;

                    cmd.Parameters.Add(new SqlParameter("@FinishDate", SqlDbType.DateTime));
                    cmd.Parameters["@FinishDate"].Value = whereParametersList[0].WhereentryDateTo;

                    cmd.CommandTimeout = 180;

                    SqlDataReader reaccount = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    while (reaccount.Read())
                    {
                        #region WorkEfficiencyQueryUnitsDays

                        staffWorkEfficiencyList.Add(new StaffWorkEfficQueryData()
                        {
                            EntryDate = Common.MyIsDBNull(reaccount, "EntryDate"),
                            OperatorCount = Common.MyToFormat(reaccount, "OperatorCount", Common.ClassType.DOUBLE , "#,##0"),
                            StatsticClass =Common.MyIsDBNull(reaccount,"StatsticClass"),

                            Duration = Common.MyToFormat(reaccount, "Duration", Common.ClassType.DOUBLE, "#,##0.00"),
                            PropagationTime = Common.MyToFormat(reaccount, "PropagationTime", Common.ClassType.DOUBLE, "#,##0.00"),
                            DutyDuration = Common.MyToFormat(reaccount, "DutyDuration", Common.ClassType.DOUBLE, "#,##0"),

                            // 稼动率
                            // Duration /DutyDuration  
                            DayEntryRate = Common.MyToFormat(Common.MyToCompute(reaccount, "Duration", "DutyDuration"), "%"),

                            // 稼动率（含上传下载）
                            // PropagationTime /DutyDuration 
                            DayProTimeEntryRate = Common.MyToFormat(Common.MyToCompute(reaccount, "PropagationTime", "DutyDuration"), "%")

                        });

                        #endregion WorkEfficiencyQueryUnitsDays
                    }

                    reaccount.Close();
                    conn.Close();
                }
                return staffWorkEfficiencyList;

                #endregion SqlCommand
            }
        }

        /// <summary>
        /// 全员别 稼动率查询 _Hour
        /// </summary>
        /// <param name="whereParametersList">The where parameters list.</param>
        /// <returns></returns>
        public static List<StaffWorkEfficQueryData> GetSelectStaffWorkEfficiencyHour(List<WhereParameters> whereParametersList)
        {
            List<StaffWorkEfficQueryData> staffHourWorkEfficiencyList = new List<StaffWorkEfficQueryData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand" 执行存储过程  WorkEfficiencyQueryStaffHours
                using (SqlCommand cmd = new SqlCommand("WorkEfficiencyQueryStaffHours", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starDate", SqlDbType.DateTime));
                    cmd.Parameters["@starDate"].Value = whereParametersList[0].WhereentryDateFrom;

                    cmd.Parameters.Add(new SqlParameter("@FinishDate", SqlDbType.DateTime));
                    cmd.Parameters["@FinishDate"].Value = whereParametersList[0].WhereentryDateTo;

                    cmd.CommandTimeout = 180;

                    SqlDataReader reaccount = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    while (reaccount.Read())
                    {
                        #region WorkEfficiencyQueryDays

                        staffHourWorkEfficiencyList.Add(new StaffWorkEfficQueryData()
                        {
                            EntryDate = Common.MyIsDBNull(reaccount, "EntryDate"),

                            ZeroOClock = Common.MyToFormat(reaccount, "0", Common.ClassType.DOUBLE, "%"),
                            OneOClock = Common.MyToFormat(reaccount, "1", Common.ClassType.DOUBLE, "%"),
                            TwoOClock = Common.MyToFormat(reaccount, "2", Common.ClassType.DOUBLE, "%"),
                            ThreeOClock = Common.MyToFormat(reaccount, "3", Common.ClassType.DOUBLE, "%"),
                            FourOClock = Common.MyToFormat(reaccount, "4", Common.ClassType.DOUBLE, "%"),
                            FiveOClock = Common.MyToFormat(reaccount, "5", Common.ClassType.DOUBLE, "%"),
                            SixOClock = Common.MyToFormat(reaccount, "6", Common.ClassType.DOUBLE, "%"),
                            SevenOClock = Common.MyToFormat(reaccount, "7", Common.ClassType.DOUBLE, "%"),
                            EightOClock = Common.MyToFormat(reaccount, "8", Common.ClassType.DOUBLE, "%"),
                            NineOClock = Common.MyToFormat(reaccount, "9", Common.ClassType.DOUBLE, "%"),
                            TenOClock = Common.MyToFormat(reaccount, "10", Common.ClassType.DOUBLE, "%"),
                            ElevenOClock = Common.MyToFormat(reaccount, "11", Common.ClassType.DOUBLE, "%"),
                            TwelveOClock = Common.MyToFormat(reaccount, "12", Common.ClassType.DOUBLE, "%"),
                            ThirteenOClock = Common.MyToFormat(reaccount, "13", Common.ClassType.DOUBLE, "%"),
                            FourteenOClock = Common.MyToFormat(reaccount, "14", Common.ClassType.DOUBLE, "%"),
                            FifteenOClock = Common.MyToFormat(reaccount, "15", Common.ClassType.DOUBLE, "%"),
                            SixteenOClock = Common.MyToFormat(reaccount, "16", Common.ClassType.DOUBLE, "%"),
                            SeventeenOClock = Common.MyToFormat(reaccount, "17", Common.ClassType.DOUBLE, "%"),
                            EighteenOClock = Common.MyToFormat(reaccount, "18", Common.ClassType.DOUBLE, "%"),
                            NineteenOClock = Common.MyToFormat(reaccount, "19", Common.ClassType.DOUBLE, "%"),
                            TwentyOClock = Common.MyToFormat(reaccount, "20", Common.ClassType.DOUBLE, "%"),
                            TwentyoneOClock = Common.MyToFormat(reaccount, "21", Common.ClassType.DOUBLE, "%"),
                            TwentytwoOClock = Common.MyToFormat(reaccount, "22", Common.ClassType.DOUBLE, "%"),
                            TwentythreeOClock = Common.MyToFormat(reaccount, "23", Common.ClassType.DOUBLE, "%")
                        });

                        #endregion WorkEfficiencyQueryStaffHours
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return staffHourWorkEfficiencyList;
                #endregion SqlCommand
            }
        }
        #endregion 全员员别 稼动率

    }
}
